﻿using Dapper;
using JESAI.MonacoRoslynComplete.Exceptions;
using JESAI.MonacoRoslynComplete.Models;
using JESAI.MonacoRoslynConplete.Storages;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;

namespace JESAI.MonacoRoslynComplete.Storage.MySql
{
    internal class MySqlStorage : IMonacoRoslynCompleteStorage
    {
        public MySqlStorageOptions Options { get; }

        public MySqlConnectionFactory ConnectionFactory { get; }

        public ILogger<MySqlStorage> Logger { get; }

        //private readonly AsyncCallbackDeferFlushCollection<IRequestModel, IRequestDetailModel> _deferFlushCollection = null;

        public MySqlStorage(IOptions<MySqlStorageOptions> options, MySqlConnectionFactory connectionFactory, ILogger<MySqlStorage> logger)
        {
            Options = options.Value;
            ConnectionFactory = connectionFactory;
            Logger = logger;
            //if (Options.EnableDefer)
            //{
            //    _deferFlushCollection = new AsyncCallbackDeferFlushCollection<IRequestModel, IRequestDetailModel>(AddRequestInfoAsync, Options.DeferThreshold, Options.DeferSecond);
            //}
        }

        #region 初始化

        public async Task InitAsync()
        {
            try
            {
                using (var connection = ConnectionFactory.GetConnection())
                {
                    await connection.ExecuteAsync(@"CREATE TABLE IF NOT EXISTS `Code_File_Tree` (
                      `Id` varchar(50) NOT NULL COMMENT '唯一标识',
                      `ParentId` varchar(50) DEFAULT NULL COMMENT '父节点Id',
                      `Name` varchar(200) DEFAULT NULL COMMENT '名称',
                      `IsValid` TINYINT(1) DEFAULT NULL COMMENT '是否有效',
                      `Type` varchar(50) default 'file' COMMENT '类型：file,floder',
                      `IsDeleted` TINYINT(1) DEFAULT NULL  COMMENT '是否删除',
                      `CreateTime` datetime(3) DEFAULT NULL  COMMENT '创建时间',
                      PRIMARY KEY (`Id`) 
                    ) COMMENT '代码文件树' ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                    await connection.ExecuteAsync($@"CREATE TABLE IF NOT EXISTS `Code_File_Content` (
                      `Id` varchar(50) NOT NULL COMMENT '唯一标识',
                      `CodeContent` text DEFAULT NULL COMMENT '代码内容',
                      `CodeFileTreeId` varchar(50) DEFAULT NULL COMMENT '代码文件树Id',
                      `CodeType` int DEFAULT NULL COMMENT '代码类型: 0-csharp, 1-vb, 2-fsharp',
                      `IsValid` TINYINT(1) DEFAULT NULL  COMMENT '是否有效',
                      `Version` int DEFAULT NULL  COMMENT '版本号',
                      `CreateTime` datetime(3) DEFAULT NULL COMMENT '创建时间',
                      PRIMARY KEY (`Id`) 
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                }
            }
            catch (Exception ex)
            {
                Logger.LogWarning(ex, $"Init Error:{ex.Message}");

                throw ex;
            }
        }

        public async Task CreateDatabaseAsync()
        {
            using (var ccon = ConnectionFactory.GetConnectionWithoutDefaultDatabase())
            {
                var num = await ccon.ExecuteAsync($"CREATE DATABASE IF NOT EXISTS `{ConnectionFactory.DataBase}` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;");
                if (num == 1)
                {
                    Logger.LogInformation($"自动创建了数据库: {ConnectionFactory.DataBase}");
                }
                else
                {
                    throw new MonacoRoslynStorageException($"自动创建数据库 {ConnectionFactory.DataBase} 异常");
                }
            }
        }

        #endregion Init

        public async Task AddCodeFileAsync(ICodeFileModel model)
        {
            await LoggingSqlOperation(async connection =>
            {
                await connection.ExecuteAsync("INSERT INTO `Code_File_Tree` (`Id`,`ParentId`,`Name`, `IsValid`, `Type`,`CreateTime`) VALUES(@Id, @ParentId, @Name, @IsValid, @Type, @CreateTime)", model);
            }, "请求数据保存失败");
        }

        public async Task AddCodeContentAsync(ICodeFileContentModel model)
        {

            await LoggingSqlOperation(async connection =>
            {
                connection.Open();
                using (var trans = connection.BeginTransaction())
                {
                    try
                    {

                        var update_sql = "update Code_File_Content set IsValid=false where CodeFileTreeId = @CodeFileTreeId and IsValid = true";

                        await connection.ExecuteAsync(update_sql, new { CodeFileTreeId = model.CodeFileTreeId });

                        var query_sql = "select * from Code_File_Content where CodeFileTreeId=@CodeFileTreeId order by Version desc limit 1";
                        var list = await QueryAsync<CodeFileContentModel>(query_sql, new { CodeFileTreeId = model.CodeFileTreeId });
                        if (list.Count > 0)
                        {
                            model.Version = list[0].Version + 1;
                        }

                        await connection.ExecuteAsync("INSERT INTO `Code_File_Content` (`Id`,`CodeContent`,`CodeFileTreeId`, `CodeType`, `IsValid`,`Version`,`CreateTime` ) VALUES (@Id, @CodeContent, @CodeFileTreeId, @CodeType, @IsValid, @Version,@CreateTime)", model);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }

            }, "请求数据保存失败");

        }

        #region 通用方法
        protected async Task LoggingSqlOperation(Func<IDbConnection, Task> func, string message = null, [CallerMemberName] string method = null)
        {
            try
            {
                using (var connection = ConnectionFactory.GetConnection())
                {
                    await func(connection);
                }
            }
            catch (Exception ex)
            {
                Logger.LogError(ex, $"Method: {method} Message: {message ?? "数据库操作异常"}");
            }
        }

        protected async Task<T> LoggingSqlOperation<T>(Func<IDbConnection, Task<T>> func, string message = null, [CallerMemberName] string method = null)
        {
            try
            {
                using (var connection = ConnectionFactory.GetConnection())
                {
                    return await func(connection);
                }
            }
            catch (Exception ex)
            {
                Logger.LogError(ex, $"Method: {method} Message: {message ?? "数据库操作异常"}");
                throw;
            }
        }

        private async Task<List<T>> QueryAsync<T>(string sql, object? param = null)
        {
            TraceLogSql(sql, param: param);
            return await LoggingSqlOperation(async connection => (await connection.QueryAsync<T>(sql, param)).ToList());
        }

        private async Task<List<T>> QueryAsync<T>(string sql)
        {
            TraceLogSql(sql, null);
            return await LoggingSqlOperation(async connection => (await connection.QueryAsync<T>(sql)).ToList());
        }

        protected void TraceLogSql(string sql, [CallerMemberName] string method = null)
        {
            Logger.LogTrace($"Class: {nameof(MySqlStorage)} Method: {method} SQL: {sql}");
        }

        protected void TraceLogSql(string sql, [CallerMemberName] string method = null, object? param = null)
        {
            Logger.LogTrace($"Class: {nameof(MySqlStorage)} Method: {method} SQL: {sql} Param: {JsonConvert.SerializeObject(param)}");
        }

        #endregion

        public async Task UpdateCodeFileAsync(ICodeFileModel codeFile)
        {
            var sql = new StringBuilder("update Code_File_Tree set ");
            if (!string.IsNullOrEmpty(codeFile.Name))
            {
                sql.Append("Name = @Name, ");
            }
            if (!string.IsNullOrEmpty(codeFile.ParentId))
            {
                sql.Append("ParentId = @ParentId, ");
            }
            if (codeFile.IsValid.HasValue)
            {
                sql.Append("IsValid = @IsValid, ");
            }
            if (!string.IsNullOrEmpty(codeFile.Type))
            {
                sql.Append("Type = @Type, ");
            }
            if (codeFile.CreateTime.HasValue)
            {
                sql.Append("CreateTime = @CreateTime, ");
            }
            sql.Remove(sql.Length - 2, 2);
            sql.Append(" where Id = @Id");

            await LoggingSqlOperation(async connection =>
            {
                await connection.ExecuteAsync(sql.ToString(), codeFile);
            }, "更新代码文件失败");
        }

        public async Task DeleteCodeFile(string id)
        {
            var sql = "update Code_File_Tree set IsDeleted = true where Id = @Id";
            await LoggingSqlOperation(async connection =>
            {
                await connection.ExecuteAsync(sql, new { Id = id });
            }, "删除代码文件失败");
        }

        public async Task MoveFileOut(string id)
        {
            var sql = "update Code_File_Tree set ParentId = null where Id = @Id";
            await LoggingSqlOperation(async connection =>
            {
                await connection.ExecuteAsync(sql, new { Id = id });
            }, "移动代码文件失败");
        }

        public async Task<List<ICodeFileModel>> GetAllCodeFileDatas()
        {
            //IsDeleted 不等于true，可能为false 也可能为null
            var sql = "select * from Code_File_Tree where IsDeleted is not true and IsValid = true";
            var list = await QueryAsync<CodeFileModel>(sql);
            return list.Cast<ICodeFileModel>().ToList();
        }

        public async Task<ICodeFileContentModel> GetCodeFileContent(string codeFileTreeId)
        {
            var sql = "select * from Code_File_Content where CodeFileTreeId = @CodeFileTreeId order by Version desc limit 1";

            return await LoggingSqlOperation(async connection =>
            {
                var list = await QueryAsync<CodeFileContentModel>(sql, new { CodeFileTreeId = codeFileTreeId });
                if (list.Count > 0)
                {
                    return list[0];
                }
                else
                {
                    return null;
                }
            }, "获取代码文件内容失败");
        }

        public async Task<List<ICodeFileModel>> GetScriptCodeFileListDatas()
        {
            var sql = "select * from Code_File_Tree where IsDeleted is not true and IsValid = true and type = 'file'";
            var list = await QueryAsync<CodeFileModel>(sql);
            return list.Cast<ICodeFileModel>().ToList();
        }
    }
}
